select a.filial_origem as origem,a.filial as destino,a.numero_nf_transferencia as nota_fiscal,
CONVERT(varchar(30), a.emissao, 103) as emissao,b.produto as produto,b.preco1 as preco,b.qtde_entrada as qtd,
b.valor as valor,b.cor_produto as cor,C.DESC_COR_PRODUTO as nome_cor_produto,
cast(
CASE WHEN B.EN1 > 0 THEN cast(' ' +ltrim(cast(B.EN1 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_1)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN2 > 0 THEN cast(' ' +ltrim(cast(B.EN2 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_2)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN3 > 0 THEN cast(' ' +ltrim(cast(B.EN3 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_3)) + ')' as varchar(10))
ELSE '' END  +
CASE WHEN B.EN4 > 0 THEN cast(' ' +ltrim(cast(B.EN4 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_4)) + ')' as varchar(10))
ELSE '' END + 
CASE WHEN B.EN5 > 0 THEN cast(' ' +ltrim(cast(B.EN5 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_5)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN6 > 0 THEN cast(' ' +ltrim(cast(B.EN6 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_6)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN7 > 0 THEN cast(' ' +ltrim(cast(B.EN7 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_7)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN8 > 0 THEN cast(' ' +ltrim(cast(B.EN8 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_8)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN9 > 0 THEN cast(' ' +ltrim(cast(B.EN9 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_9)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN10 > 0 THEN cast(' ' +ltrim(cast(B.EN10 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_10)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN11 > 0 THEN cast(' ' +ltrim(cast(B.EN11 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_11)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN12 > 0 THEN cast(' ' +ltrim(cast(B.EN12 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_12)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN13 > 0 THEN cast(' ' +ltrim(cast(B.EN13 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_13)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN14 > 0 THEN cast(' ' +ltrim(cast(B.EN14 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_14)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN15 > 0 THEN cast(' ' +ltrim(cast(B.EN15 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_15)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN16 > 0 THEN cast(' ' +ltrim(cast(B.EN16 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_16)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN17 > 0 THEN cast(' ' +ltrim(cast(B.EN17 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_17)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN18 > 0 THEN cast(' ' +ltrim(cast(B.EN18 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_18)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN19 > 0 THEN cast(' ' +ltrim(cast(B.EN19 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_19)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN20 > 0 THEN cast(' ' +ltrim(cast(B.EN20 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_20)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN21 > 0 THEN cast(' ' +ltrim(cast(B.EN21 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_21)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN22 > 0 THEN cast(' ' +ltrim(cast(B.EN22 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_22)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN23 > 0 THEN cast(' ' +ltrim(cast(B.EN23 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_23)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN24 > 0 THEN cast(' ' +ltrim(cast(B.EN24 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_24)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN25 > 0 THEN cast(' ' +ltrim(cast(B.EN25 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_25)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN26 > 0 THEN cast(' ' +ltrim(cast(B.EN26 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_26)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN27 > 0 THEN cast(' ' +ltrim(cast(B.EN27 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_27)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN28 > 0 THEN cast(' ' +ltrim(cast(B.EN28 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_28)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN29 > 0 THEN cast(' ' +ltrim(cast(B.EN29 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_29)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN30 > 0 THEN cast(' ' +ltrim(cast(B.EN30 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_30)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN31 > 0 THEN cast(' ' +ltrim(cast(B.EN31 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_31)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN32 > 0 THEN cast(' ' +ltrim(cast(B.EN32 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_32)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN33 > 0 THEN cast(' ' +ltrim(cast(B.EN33 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_33)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN34 > 0 THEN cast(' ' +ltrim(cast(B.EN34 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_34)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN35 > 0 THEN cast(' ' +ltrim(cast(B.EN35 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_35)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN36 > 0 THEN cast(' ' +ltrim(cast(B.EN36 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_36)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN37 > 0 THEN cast(' ' +ltrim(cast(B.EN37 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_37)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN38 > 0 THEN cast(' ' +ltrim(cast(B.EN38 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_38)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN39 > 0 THEN cast(' ' +ltrim(cast(B.EN39 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_39)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN40 > 0 THEN cast(' ' +ltrim(cast(B.EN40 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_40)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN41 > 0 THEN cast(' ' +ltrim(cast(B.EN41 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_41)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN42 > 0 THEN cast(' ' +ltrim(cast(B.EN42 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_42)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN43 > 0 THEN cast(' ' +ltrim(cast(B.EN43 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_43)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN44 > 0 THEN cast(' ' +ltrim(cast(B.EN44 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_44)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN45 > 0 THEN cast(' ' +ltrim(cast(B.EN45 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_45)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN46 > 0 THEN cast(' ' +ltrim(cast(B.EN46 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_46)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN47 > 0 THEN cast(' ' +ltrim(cast(B.EN47 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_47)) + ')' as varchar(10))
ELSE '' END +
CASE WHEN B.EN48 > 0 THEN cast(' ' +ltrim(cast(B.EN48 as varchar(4))) + ' (' + rtrim(ltrim(E.TAMANHO_48)) + ')' as varchar(10))
ELSE '' END 
as varchar(400)) as qtd_tam
from loja_entradas a
join LOJA_ENTRADAS_PRODUTO b on (a.romaneio_produto = b.romaneio_produto and a.filial = b.filial)
join PRODUTO_CORES C on (B.produto = C.produto and B.cor_produto = C.cor_produto)
join PRODUTOS D on (C.produto = D.produto)
JOIN PRODUTOS_TAMANHOS E ON (D.GRADE = E.GRADE)
where substring(a.filial,1,3) = '525'
and substring(a.filial_origem,1,3) in ('095','110','113','124','128','519','999')
and datepart(yyyy,a.emissao) = 2008
and (
(substring(a.filial_origem,1,3) = '519' and cast(a.numero_nf_transferencia as int) in (00000896
,00000897
,00000898
,00000899
,00000900)) or
(substring(a.filial_origem,1,3) = '095' and cast(a.numero_nf_transferencia as int) in (001068  
,001069  
,001070  
,001073  
,001074  
,001075  
,001076  
,001077  
,001079  
,001080  
,001081  
,001082  
,001083  
,001084)) or 
(substring(a.filial_origem,1,3) = '110' and cast(a.numero_nf_transferencia as int) in (002047  
,002052  
,002061  
,002064  
,002065  
,002067  
,002068  
,002069  
,002070  
,002071  
,002073  
,002074  
,002078  
,002079  
,002081  
,002085  
,002086  
,002087)) or
(substring(a.filial_origem,1,3) = '113' and cast(a.numero_nf_transferencia as int) in (000964  
,000965  
,000663  
,000668  
,273730)) or  
(substring(a.filial_origem,1,3) = '124' and cast(a.numero_nf_transferencia as int) in (000741  
,000742  
,000743  
,000745  
,000746  
,000748  
,000750  
,000751  
,000752  
,000753  
,000756  
,000757  
,000760  
,000761  
,000765  
,000766  
,000767  
,000768  
,000769  
,000772  
,000773  
,000774  
,000775)) or   
(substring(a.filial_origem,1,3) = '128' and cast(a.numero_nf_transferencia as int) in (000711,000713)) or 
(substring(a.filial_origem,1,3) = '999' and rtrim(ltrim(a.numero_nf_transferencia)) in ('280218','280496','280508','280981','281647')))
order by a.filial_origem,a.numero_nf_transferencia

select a.filial_origem as origem,numero_nf_transferencia,sum(b.qtde_entrada) as qtd,
sum(b.valor) as valor
from loja_entradas a
join LOJA_ENTRADAS_PRODUTO b on (a.romaneio_produto = b.romaneio_produto and a.filial = b.filial)
where substring(a.filial,1,3) = '525'
and substring(a.filial_origem,1,3) in ('095','110','113','124','128','519','999')
and datepart(yyyy,a.emissao) = 2008
and (
(substring(a.filial_origem,1,3) = '519' and cast(a.numero_nf_transferencia as int) in (00000896
,00000897
,00000898
,00000899
,00000900)) or
(substring(a.filial_origem,1,3) = '095' and cast(a.numero_nf_transferencia as int) in (001068  
,001069  
,001070  
,001073  
,001074  
,001075  
,001076  
,001077  
,001079  
,001080  
,001081  
,001082  
,001083  
,001084)) or 
(substring(a.filial_origem,1,3) = '110' and cast(a.numero_nf_transferencia as int) in (002047  
,002052  
,002061  
,002064  
,002065  
,002067  
,002068  
,002069  
,002070  
,002071  
,002073  
,002074  
,002078  
,002079  
,002081  
,002085  
,002086  
,002087)) or
(substring(a.filial_origem,1,3) = '113' and cast(a.numero_nf_transferencia as int) in (000964  
,000965  
,000663  
,000668  
,273730)) or  
(substring(a.filial_origem,1,3) = '124' and cast(a.numero_nf_transferencia as int) in (000741  
,000742  
,000743  
,000745  
,000746  
,000748  
,000750  
,000751  
,000752  
,000753  
,000756  
,000757  
,000760  
,000761  
,000765  
,000766  
,000767  
,000768  
,000769  
,000772  
,000773  
,000774  
,000775)) or   
(substring(a.filial_origem,1,3) = '128' and cast(a.numero_nf_transferencia as int) in (000711,000713)) or 
(substring(a.filial_origem,1,3) = '999' and rtrim(ltrim(a.numero_nf_transferencia)) in ('280218','280496','280508','280981','281647')))
group by a.filial_origem,a.numero_nf_transferencia
order by filial_origem,numero_nf_transferencia

---------------
--- obtendo as lojas de origem
---------------

select filial_origem,numero_nf_transferencia
from loja_entradas 
where  substring(filial,1,3) = '525'
and datepart(yyyy,emissao) = 2008
and numero_nf_transferencia in (
'00000896',
'00000897',
'00000898',
'00000899',
'00000900',
'000663',
'000668',
'000711',
'000713',
'000741',
'000742',
'000743',
'000745',
'000746',
'000748',
'000750',
'000751',
'000752',
'000753',
'000756',
'000757',
'000760',
'000761',
'000765',
'000766',
'000767',
'000768',
'000769',
'000772',
'000773',
'000774',
'000775',
'000964',
'000965',
'001068',
'001069',
'001070',
'001073',
'001074',
'001075',
'001076',
'001077',
'001079',
'001080',
'001081',
'001082',
'001083',
'001084',
'002047',
'002052',
'002061',
'002064',
'002065',
'002067',
'002068',
'002069',
'002070',
'002071',
'002073',
'002074',
'002078',
'002079',
'002081',
'002085',
'002086',
'002087',
'273730',
'280218',
'280496',
'280508',
'280521',
'280981',
'281647')
order by filial_origem,numero_nf_transferencia


select filial_origem,numero_nf_transferencia,CONVERT(varchar(30), emissao, 103) as emissao 
from loja_entradas 
where  substring(filial,1,3) = '525'
and numero_nf_transferencia in ('000756')
and TIPO_ENTRADA_SAIDA = '01'
and datepart(yyyy,emissao) = 2008
order by filial_origem,numero_nf_transferencia




---------------------------------------




filial_origem             numero_nf_transferencia
------------------------- -----------------------
095_FASHION MALL II       001068                 
095_FASHION MALL II       001069                 
095_FASHION MALL II       001070                 
095_FASHION MALL II       001073                 
095_FASHION MALL II       001074                 
095_FASHION MALL II       001075                 
095_FASHION MALL II       001076                 
095_FASHION MALL II       001077                 
095_FASHION MALL II       001079                 
095_FASHION MALL II       001080                 
095_FASHION MALL II       001081                 
095_FASHION MALL II       001082                 
095_FASHION MALL II       001083                 
095_FASHION MALL II       001084                 
110_NORTESHOPPING         002047                 
110_NORTESHOPPING         002052                 
110_NORTESHOPPING         002061                 
110_NORTESHOPPING         002064                 
110_NORTESHOPPING         002065                 
110_NORTESHOPPING         002067                 
110_NORTESHOPPING         002068                 
110_NORTESHOPPING         002069                 
110_NORTESHOPPING         002070                 
110_NORTESHOPPING         002071                 
110_NORTESHOPPING         002073                 
110_NORTESHOPPING         002074                 
110_NORTESHOPPING         002078                 
110_NORTESHOPPING         002079                 
110_NORTESHOPPING         002081                 
110_NORTESHOPPING         002085                 
110_NORTESHOPPING         002086                 
110_NORTESHOPPING         002087                 
113_TIJUCA                000964                 
113_TIJUCA                000965                 
114_LEBLON                000663                 
114_LEBLON                000668                 
114_LEBLON                273730                 
124_RIO DESIGN            000741                 
124_RIO DESIGN            000742                 
124_RIO DESIGN            000743                 
124_RIO DESIGN            000745                 
124_RIO DESIGN            000746                 
124_RIO DESIGN            000748                 
124_RIO DESIGN            000750                 
124_RIO DESIGN            000751                 
124_RIO DESIGN            000752                 
124_RIO DESIGN            000753                 
124_RIO DESIGN            000756                 
124_RIO DESIGN            000757                 
124_RIO DESIGN            000760                 
124_RIO DESIGN            000761                 
124_RIO DESIGN            000765                 
124_RIO DESIGN            000766                 
124_RIO DESIGN            000767                 
124_RIO DESIGN            000768                 
124_RIO DESIGN            000769                 
124_RIO DESIGN            000772                 
124_RIO DESIGN            000773                 
124_RIO DESIGN            000774                 
124_RIO DESIGN            000775                 
128_BOTAFOGO              000711                 
128_BOTAFOGO              000713                 
519_VITORIA               00000896               
519_VITORIA               00000897               
519_VITORIA               00000898               
519_VITORIA               00000899               
519_VITORIA               00000900               
999_CDLOJAS               280218                 
999_CDLOJAS               280496                 
999_CDLOJAS               280508                 
999_CDLOJAS               280521                 
999_CDLOJAS               280981                 
999_CDLOJAS               281647                 